PostgreSQL, display role members

Question

Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like

role: council_stuff
members:
    Harry
    Ben
    Steve
    Melinda

Answer 1

To get all members of all roles:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)                                  
WHERE r.rolcanlogin
ORDER BY 1;

Add r1.rolname='council_stuff' to filter on only that one.

Be aware that users themselves are "roles" in postgresql parlance (there used to be "users and groups" in older versions, but this have been unified into roles).

Here the difference is made between a user and a role with rolcanlogin boolean column, which corresponds o the interpretation that a user is a role that has the LOGIN permission.

The two JOIN can be turned into LEFT JOIN to add the users who aren't member of any role:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)                                  
WHERE r.rolcanlogin
ORDER BY 1;

Answer 2

In more of the query of Daniel Vérité

If you have many roles to a member you can aggregate all roles

WITH z AS (
SELECT m.member, ARRAY_AGG(r1.rolname) role
FROM pg_catalog.pg_auth_members m
JOIN pg_roles r1 ON (m.roleid=r1.oid) GROUP BY 1
)
SELECT r.rolname as username,z.role
FROM pg_catalog.pg_roles r
LEFT JOIN z on z.member = r.oid
WHERE r.rolcanlogin
ORDER BY 1;